# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html

import re
import pymysql


def stringToInt(string):
    patten = "[-+]?[0-9]*\.?[0-9]+"
    a = float(re.search(patten, string).group())
    if '万' in string:
        a = a * 10000
    return int(a)


def insertBook(title, author, cover,
               clickTotal, clickMonth, clickWeek,
               recommendTotal, recommendMonth, recommendWeek,
               wordTotal, monthTicketTotal, collect):
    db = pymysql.connect(host='localhost', user='root', passwd='A1UH6kDp0Fxc', db='ciweimao', charset='utf8')

    cursor = db.cursor()

    sql = "INSERT INTO ciweimao.books\
    (title, author, cover, \
    clickTotal, clickMonth, clickWeek, \
    recommendTotal, recommendMonth, recommendWeek, \
    wordTotal, monthTicketTotal, collect) \
    values('%s', '%s', '%s', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i', '%i')\
    " % \
          (title, author, cover,
           clickTotal, clickMonth, clickWeek,
           recommendTotal, recommendMonth, recommendWeek,
           wordTotal, monthTicketTotal, collect)

    try:
        # 执行sql语句
        cursor.execute(sql)
        db.commit()
    except:
        print('exception')

    db.close()


class HbookerscrapyPipeline(object):
    def process_item(self, item, spider):
        clickTotal = stringToInt(item.get('clickTotal')[0])
        clickMonth = stringToInt(item.get('clickMonth')[0])
        clickWeek = stringToInt(item.get('clickWeek')[0])

        recommendTotal = stringToInt(item.get('recommendTotal')[0])
        recommendMonth = stringToInt(item.get('recommendMonth')[0])
        recommendWeek = stringToInt(item.get('recommendWeek')[0])

        wordTotal = stringToInt(item.get('wordTotal')[0])
        monthTicketTotal = stringToInt(item.get('monthTicketTotal')[0])
        collect = stringToInt(item.get('collect')[0])

        insertBook(item['title'][0], item['author'][0], item['cover'][0],
                   clickTotal, clickMonth, clickWeek,
                   recommendTotal, recommendMonth, recommendWeek,
                   wordTotal, monthTicketTotal, collect)
        return item
